データ移行における『テーブルデータ定義の型変換』のまとめ(MySQL→Amazon Redshift編)

しんや

2015.08.26

この記事は公開されてから1年以上経過しています。情報が古い可能性がありますので、ご注意ください。

Amazon Redshiftへデータを投入・移行させる際のデータソースについては、様々な種類のデータソースが想定される事と思います。中でも『オンプレ環境のRDBデータ』というケースは結構な割合で想定されるのではないでしょうか。

Amazon Redshiftに対し、異なるデータソースからのデータを投入させる場合、当然の事ながら『データベーステーブルのカラムに於ける型の定義』はデータソース間でも微妙に異なったりします。移行元データソースのテーブル定義をそのままAmazon Redshiftのテーブル定義に当てはめる事が出来れば一番効率が良いのですが、中々そう上手くは行かない部分もあったりします。

そこで当エントリでは、移行元データソースのテーブル定義に対応するAmazon Redshiftのテーブル定義にはどのようなものがあるのか、データ型間の対応可否はどの様になっているのかという観点で情報をまとめてみたいと思います。当エントリでご紹介するのは『MySQL』と『Amazon Redshift』の対応となります。

データ型	別名	説明
SMALLINT	INT2	符号付き 2 バイト整数
INTEGER	INT、INT4	符号付き 4 バイト整数
BIGNIT	INT8	符号付き 8 バイト整数
DECIMAL	NUMERIC	精度の選択が可能な真数
REAL	FLOAT4	単精度浮動小数点数
DOUBLE PRECISION	FLOAT8、FLOAT	倍精度浮動小数点数
BOOLEAN	BOOL	論理ブール演算型（true/false）
CHAR	CHARACTERNCHAR、BPCHAR	固定長のキャラクタ文字列
VARCHAR	CHARACTER VARYING、NVARCHAR、TEXT	ユーザーによって定義された制限を持つ可変長キャラクタ文字列
DATE		カレンダー日付（年、月、日）
TIMESTAMP	TIMESTAMP WITHOUT TIME ZONE	日付と時刻（タイムゾーンなし）

データ型比較・変換対応表(MySQL → Amazon Redshift)

次いで、MySQLとAmazon Redshiftの各種データ型をそれぞれ見て行きたいと思います。主に以下の情報を参考にしました。

数値型

数値型に関するMySQLとAmazon Redshiftのデータ型については以下の通りです。基本的には対応する桁数が双方に存在しているので変換自体は特に手を煩わされる事は無さそうですね。

MySQL			Amazon Redshift
データ型分類	データ型	データ範囲	データ型	データ範囲
整数型	TINYINT	-128から127 (符号無しの場合0から255)	SMALLINT INT2	-32768～+32767
	SMALLINT	-32768から32767 (符号無しの場合0から65535)	SMALLINT INT2	-32768～+32767
	MEDIUMINT	-8388608から8388607 (符号無しの場合 0から16777215)	INT INTEGER INT4	-2147483648～+2147483647
	INT INTEGER	-2147483648〜 2147483647 (符号無しの場合 0から4294967295)	INT INTEGER INT4	-2147483648～+2147483647
	BIGINT	-9223372036854775808 〜 9223372036854775807 (符号無しの場合 0から 18446744073709551615)	BIGINT INT8	-9223372036854775808 ～ 9223372036854775807
固定小数点型	DECIMAL	任意の精度及びスケール指定 (整数部小数部併せて65桁まで)	DECIMAL	precision(精度)：デフォルト(指定無し)=18、最大=38 scale(スケール)：デフォルト(指定無し)=0、最大=37
固定小数点型	NUMERIC	(同上)	NUMERIC	(同上)
浮動小数点型	FLOAT	-3.402823466E+38〜 -1.175494351E-38 0 1.175494351E-38〜3.402823466E+38	REAL FLOAT4	4バイト有効な精度桁数:6桁
浮動小数点型	DOUBLE	-1.7976931348623157E+308〜 -2.2250738585072014E-308 0 2.2250738585072014E-308〜 1.7976931348623157E+308	DOUBLE PRECISION FLOAT8</br/>FLOAT	8バイト有効な精度桁数:15桁

真偽(BOOLEAN)型

Amazon Redshiftに於ける真偽(BOOLEAN)型に対応するMySQLのデータ型として、BOOLEAN型の項目は定義は出来るようです。しかし定義後の値としてはTINYINT型の1桁の項目として処理される模様。この事から、BOOLEANで定義されているデータをAmazon Redshiftに移行する場合は(1).そのまま数値型として取り込む (2). 一旦数値型として取り込んだ後、RedshiftのBOOLEAN型に変換を掛けるELT処理を行う (3).BIツール等で用いる場合は、いっその事1/0を何らかの意味ある文字列に置き換えてしまう(『1:はい、0:いいえ』等)等の対応を取る事が選択肢として挙げられるのではないでしょうか。

日付時刻型

日付及び時刻に関するMySQLとAmazon Redshiftのデータ型については以下の通りです。

MySQL			Amazon Redshift
データ型分類	データ型	データ範囲	データ型	データ範囲
日付時刻型	DATE	フォーマット: 'YYYY-MM-DD' 範囲: '1000-01-01'〜 '9999-12-31'	DATE	4バイト 4713BC～294276AD
	DATETIME	フォーマット: 'YYYY-MM-DD HH:MM:SS' 範囲: '1000-01-01 00:00:00'〜 '9999-12-31 23:59:59'	TIMESTAMP	8バイト 4713BC～294276AD
	TIMESTAMP	フォーマット: 'YYYY-MM-DD HH:MM:SS' 範囲: '1970-01-01 00:00:01'〜 '2037-12-31 23:59:59'	TIMESTAMP	8バイト 4713BC～294276AD
	TIME	フォーマット: 'HH:MM:SS' 範囲: '-838:59:59'〜 '838:59:59'	----	(対応データ型無し)
	YEAR[(2\|4)]	フォーマット:YYYY 範囲: 1901〜2155 0000(4桁の場合) 範囲: 70〜69 (2桁の場合、 70は1970を表し 69は2069を表す) デフォルトは4桁	----	(対応データ型無し)

MySQLに於けるDATE型, DATETIME型, TIMESTAMP型については、そのままAmazon RedshiftのDATE型, TIMESTAMP型に置き換えられるかと思います。

一方、この種類に於けるTIME型, YEAR[(2|4)]型については情報が部分的なものになっており、Tabelau等で扱うには情報として不十分です。Tableauでは日付型やタイムスタンプ型の項目になっていれば自動で型を認識し、ドリルダウン・展開可能な形式に便利な形に扱うように対応させる事が出来ますので、まずは一旦データとして取り込み(このタイミングでは一旦0埋めの文字列として整えておく形が良いでしょうか)、その後に(恐らくはレコードの他の項目として用意されている)日付時刻情報の値と合わせ、DATE型、TIMESTAMP型の値として生成し直す事をお勧めします。

上記同様に、日付項目が年(YYYY)/月(MM)/日(DD)の"文字列型"項目として分割管理されている場合も同様です。一旦情報として取り込んだ後にそれらの文字列を結合し、日付型/タイムスタンプ型項目として生成しなおす事をお勧めします。

文字列型

文字列型に関するMySQLとAmazon Redshiftのデータ型については以下の通りです。

MySQL			Amazon Redshift
データ型分類	データ型	データ範囲	データ型	データ範囲
文字列型	CHAR(M)	固定長文字列 Mは文字数を指定、 0から255文字	CHAR	4096バイト
	VARCHAR(M)	可変長文字列 Mはバイト数を指定、 0から65535バイト	VARCHAR	65535バイト (64K-1)
	ENUM	MySQL :: MySQL 5.6 リファレンスマニュアル :: 11.7 データ型のストレージ要件	----	(対応データ型無し)
	SET	MySQL :: MySQL 5.6 リファレンスマニュアル :: 11.4.5 SET 型	----	(対応データ型無し)

ENUM型, SET型については対応するデータ型はAmazon Redshiftには存在しません。エクスポートもしくはエクスポート後のタイミングで、Amazon Redshiftで扱える形にバラし、Redshiftに投入せざるを得ないのでは無いかと思います。

また、VARCHAR型の移行についてはMySQLとAmazon Redshiftでは考え方が異なるので注意してください。MySQLでのVARCHAR型は1文字に1桁を要し、Amazon Redshiftに於けるVARCHARは1文字格納するためには、文字に対応したバイトを数必要とします。

実際に試してみましょう。MySQLに於けるVARCHAR型項目を定義してみます。以下例では10桁のVARCHAR型項目に対し、全角文字(ひらがな)を10文字まで格納出来ています。11文字以上を投入しようとするとWARNINGとなり、桁数以上の部分は切り捨てられてしまいました。

mysql> CREATE DATABASE stringtestdb CHARACTER SET sjis;
Query OK, 1 row affected (0.00 sec)

mysql> USE stringtestdb;
Database changed
mysql> 
mysql> CREATE TABLE strings_test_mysql (
    ->      id INT NOT NULL,
    ->      name VARCHAR(10) NOT NULL
    -> );
Query OK, 0 rows affected (0.01 sec)

mysql> USE stringtestdb;
Database changed
mysql> INSERT INTO strings_test_mysql VALUES(1, 'あいうえおかきくけこ');
Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO strings_test_mysql VALUES(2, 'あいうえおかきくけこさ');
Query OK, 1 row affected, 1 warning (0.00 sec)

mysql> SELECT * FROM strings_test_mysql;
+----+--------------------------------+
| id | name                           |
+----+--------------------------------+
|  1 | あいうえおかきくけこ           |
|  2 | あいうえおかきくけこ           |
+----+--------------------------------+
2 rows in set (0.00 sec)

mysql>

ちなみに環境構築の手順については以下の通りです。EC2環境を1つ用意し、その中にMySQLをインストールした形を想定しました。

$ sudo yum -y update
$ sudo yum install -y mysql mysql-server
$ sudo chkconfig mysqld on
$ sudo service mysqld start
Starting mysqld:                                           [  OK  ]

$ mysql -u root
mysql> update mysql.user set password=password('adminadmin') where user = 'root';
mysql> flush privileges;
mysql> exit
$
$ mysql -u root -p
Enter password: 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 4
Server version: 5.5.42 MySQL Community Server (GPL)
 
Copyright (c) 2000, 2015, Oracle and/or its affiliates. All rights reserved.
 
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
 
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
 
mysql>

次いで、Amazon Redshiftで同じような環境を再現してみます。同じ桁数でテーブルを作成し、データを投入してみていますが、MySQLの時とは異なり全角文字(ひらがな)は3文字まで、半角文字は10桁までしか入りません。

# CREATE TABLE strings_test_redshift (
     id INT NOT NULL,
     name VARCHAR(10) NOT NULL
 );
CREATE TABLE
# INSERT INTO strings_test_redshift VALUES(1,'あいうえおかきくけこ');
ERROR:  value too long for type character varying(10)
# INSERT INTO strings_test_redshift VALUES(1,'あいうえ');
ERROR:  value too long for type character varying(10)
# INSERT INTO strings_test_redshift VALUES(1,'あいう');
INSERT 0 1

# INSERT INTO strings_test_redshift VALUES(2,'ABCDEFGHIJ');
INSERT 0 1
# INSERT INTO strings_test_redshift VALUES(2,'ABCDEFGHIJK');
ERROR:  value too long for type character varying(10)

# SELECT * FROM strings_test_redshift;
 id |    name    
----+------------
  1 | あいう
  2 | ABCDEFGHIJ
(2 rows)

#

この事から、MySQLからAmazon Redshiftにデータを移行する際、VARCHAR型の桁数については[MySQLに於ける桁数]x3をAmazon Redshiftに於ける桁数とする等の対応が必要になるかと思われます。

ちなみにAmazon RedshiftのVARCHAR型では最大4バイトまで対応している模様。MySQLで4バイト文字を扱っており、且つ移行するという場合はこの辺り臨機応変に対応する必要がありそうです。

マルチバイト文字/データ型 - Amazon Redshift

バイナリ・バイト型

バイナリ型・バイト型に関するMySQLとAmazon Redshiftのデータ型については以下の通りです。

MySQL			Amazon Redshift
データ型分類	データ型	データ範囲	データ型	データ範囲
バイナリ・バイト型	BINARY(M)	固定長バイナリバイト文字列 Nはバイトを指定、 0から255文字	----	(対応データ型無し)
バイナリ・バイト型	VARBINARY(M)	可変長バイナリバイト文字列 Mはバイト数を指定、 0から65535バイト	----	(対応データ型無し)

Amazon Redshiftに於いてはバイナリ・バイト型に相当するデータ型はありません。

BLOB・TEXT型

BLOB・TEXT型に関するMySQLとAmazon Redshiftのデータ型については以下の通りです。

MySQL			Amazon Redshift
データ型分類	データ型	データ範囲	データ型	データ範囲
BLOB/TEXT型	TINYBLOB	最長255(2の8乗-1)バイト	----	(対応データ型無し)
	BLOB(M)	最長65,535 (2の16乗-1)バイト	----	(対応データ型無し)
	MEDIUMBLOB	最長16,777,215 (2の24乗-1)バイト	----	(対応データ型無し)
	LONGLOB	最長4,294,967,295、または 4GB(2の32乗-1)バイト	----	(対応データ型無し)
	TINYTEXT	最長255(2の8乗-1)バイト	TEXT	260バイト TEXT 型および BPCHAR 型/文字型 - Amazon Redshift
	TEXT(M)	最長65,535 (2の16乗-1)バイト	VARCHAR	65535バイト (64K-1)
	MEDIUMTEXT	最長16,777,215 (2の24乗-1)バイト	----	(対応データ型無し)
	LONGTEXT	最長4,294,967,295、または 4GB(2の32乗-1)バイト	----	(対応データ型無し)

Amazon Redshiftに於いてはBLOB型に相当するデータ型はありません。

TINYTEXT/TEXT型については、対応する型に移行する事は出来そうですが、MEDIUMTEXT/LONGTEXTについては最大値に於いてAmazon Redshiftの対応型が存在していません。この辺りは何らか考慮しておく必要があるかと思われます。

まとめ

以上、MySQLとAmazon Redshiftに於けるデータ移行の際のデータ型変換のまとめでした。MySQLのデータをAmazon Redshiftで扱うためには変換などが必要なケース、また投入自体を(そのままのデータ型では)諦めざるを得ないケースが出て来そうです。MySQLからエクスポートした後にETL処理で、またエクスポートしたデータを一旦そのままAmazon Redshiftにインポートした後にELT処理で変換を掛け、Redshiftで扱えるような形のデータを構成する検討は必要となるでしょう。

当エントリではMySQL→Amazon Redshiftについてまとめてみましたが、その他の主要なRDBMSについても今後まとめてみたいと思います。こちらからは以上です。